package com.asggo.g21.utils;

import static org.assertj.core.api.Assertions.assertThat;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.StopWatch;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.core.util.ObjUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.anwen.mongo.conditions.query.QueryWrapper;
import com.anwen.mongo.mapper.MongoPlusMapMapper;
import com.asggo.g21.payload.lann.DemoData;
import com.asggo.g21.payload.lann.NoModelDataListenerV2;
import com.asggo.g21.payload.lann.NoModelDataListenerV3;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Set;
import java.util.TreeSet;
import java.util.stream.Collectors;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.core.JdbcTemplate;

/**
 * Created by IntelliJ IDEA.
 *
 * @author eric 2024/1/9 10:51
 */
@SpringBootTest
@Slf4j
class EasyExcelTests {

  @Autowired
  JdbcTemplate jdbcTemplate;
  @Autowired
  MongoPlusMapMapper mongoPlusMapMapper;
  String[] sheetArr = new String[]{
      "202209",
      "202210",
      "202211",
      "202212",
      "202301",
      "202302",
      "202303",
      "202304",
      "202305",
      "202306",
      "202307",
      "202308",
      "202309",
      "202310",
      "202311",
      "202312",
      "202401",
      "202402",
      "202403",
      "202404",
      "202405",
      "202406",
      "202407",
      "202408",
      "202409",
      "202410"
  };
  String[] monthArr = new String[]{
      "2022-09",
      "2022-10",
      "2022-11",
      "2022-12",
      "2023-01",
      "2023-02",
      "2023-03",
      "2023-04",
      "2023-05",
      "2023-06",
      "2023-07",
      "2023-08",
      "2023-09",
      "2023-10",
      "2023-11",
      "2023-12",
      "2024-01",
      "2024-02",
      "2024-03",
      "2024-04",
      "2024-05",
      "2024-06",
      "2024-07",
      "2024-08",
      "2024-09",
      "2024-10"
  };

  /**
   * 最简单的写
   * <p>
   * 1. 创建excel对应的实体对象 参照{@link DemoData}
   * <p>
   * 2. 直接写即可
   */
  @Test
  void simpleWrite() {
    // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入

    // 写法1 JDK8+
    String fileName = "d://" + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
    // 分页查询数据
    EasyExcel.write(fileName, DemoData.class)
        .sheet("模板")
        .doWrite(data(10, 1));

    assertThat(fileName).isNotNull();
  }

  /**
   * 重复多次写入
   * <p>
   * 1. 创建excel对应的实体对象 参照{@link DemoData}
   * <p>
   * 2. 使用{@link ExcelProperty}注解指定复杂的头
   * <p>
   * 3. 直接调用二次写入即可
   */
  @Test
  void repeatedWrite() {
    StopWatch sw = new StopWatch();
    sw.start();
    // 方法1: 如果写到同一个sheet
    String fileName = "d://" + "repeatedWrite" + System.currentTimeMillis() + ".xlsx";
    // 这里 需要指定写用哪个class去写
    try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
      // 这里注意 如果同一个sheet只要创建一次
      WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
      // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
      int total = 1000000;
      for (int i = 1; i <= total / 5000; i++) {
        // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
        List<DemoData> data = data(5000, i);
        excelWriter.write(data, writeSheet);
      }
    }
    sw.stop();
    System.out.println("time ->" + sw.getTotalTimeMillis());
    assertThat(sw).isNotNull();
  }

  List<DemoData> data(int pageSize, int pageNum) {
    return jdbcTemplate
        .query(
            "select ID, ORDER_ID, GUID,PROD_TYPE, PROD_NAME, PROD_AMT, EMP_ID, CREATE_DATE from order_detail limit ? offset ?",
            (rs, rowNum) -> {
              DemoData data = new DemoData();
              data.setId(rs.getInt(1));
              data.setOrderId(rs.getInt(2));
              data.setGuid(rs.getString(3));
              data.setProdType(rs.getInt(4));
              data.setProdName(rs.getString(5));
              data.setProdAmt(rs.getBigDecimal(6));
              data.setEmpId(rs.getInt(7));
              data.setCreateDate(new Date(rs.getDate(8).getTime()));

              return data;
            }, pageSize, (pageNum - 1) * pageSize);
  }

  /**
   * 不创建对象的读
   */
  @Test
  void noModelRead() {
    final InputStream resourceAsStream = this.getClass().getClassLoader()
        .getResourceAsStream("7榜单情况.xlsx");

    EasyExcel.read(resourceAsStream, new NoModelDataListenerV2("202407", mongoPlusMapMapper))
        .sheet().doRead();

    assertThat(resourceAsStream).isNotNull();
  }

  /**
   * 20240820 重新处理.
   */
  @Test
  void init20240820() throws FileNotFoundException {
    final InputStream resourceAsStream = new FileInputStream(
        "D:\\远元经营分11月.xlsx");

    assertThat(resourceAsStream).isNotNull();

    String sheetName = "202411";
    EasyExcel.read(resourceAsStream, new NoModelDataListenerV2(sheetName, mongoPlusMapMapper))
        .sheet().doRead();
  }

  @Test
  void init202406() throws FileNotFoundException {
    final InputStream resourceAsStream = new FileInputStream(
        "D:\\DP\\原始数据\\2024\\6.xlsx");

    assertThat(resourceAsStream).isNotNull();

    String sheetName = "202406";
    EasyExcel.read(resourceAsStream, new NoModelDataListenerV2(sheetName, mongoPlusMapMapper))
        .sheet().doRead();
  }

  /**
   * 二次处理点评数据.
   */
  @Test
  void processDp() {
    String[] sheetArrTemp = new String[]{
//        "202210",
//        "202211",
//        "202212",
//        "202301",
//        "202302",
//        "202303",
//        "202304",
//        "202305",
        "202306",
//        "202307",
//        "202308",
//        "202309",
//        "202310",
//        "202311",
//        "202312",
//        "202401",
//        "202402",
//        "202403",
//        "202404",
//        "202405",
//        "202406",
//        "202407"
    };

    String[] monthArrTemp = new String[]{
//        "2022-10",
//        "2022-11",
//        "2022-12",
//        "2023-01",
//        "2023-02",
//        "2023-03",
//        "2023-04",
//        "2023-05",
        "2023-06",
//        "2023-07",
//        "2023-08",
//        "2023-09",
//        "2023-10",
//        "2023-11",
//        "2023-12",
//        "2024-01",
//        "2024-02",
//        "2024-03",
//        "2024-04",
//        "2024-05",
//        "2024-06",
//        "2024-07"
    };

    List<Map<String, Object>> preAllData = getAllData("2023-05");
    // 只在第一次需要
    // saveDp2First(preAllData)

    for (int i = 0; i < sheetArrTemp.length; i++) {
      final String sheetName = sheetArrTemp[i];
      log.info("正在处理{}", sheetName);
      String headKey = "dp:" + sheetName + ":head";

      List<Map<String, Object>> batch = new ArrayList<>();

      // 表头
      final List<String> headCols = RedisUtils.getCacheList(headKey);
      // 补充是否新开、本月存在且关店、下月非正常经营
      appendHead(headCols);

      // 这个sheet页的全部数据
      List<Map<String, Object>> data = getAllData(monthArrTemp[i]);

      // 上个月有的数据，这个月没有，那么不补充到本月来，仅在上个月的历史数据中增加一个字段：“下月非正常经营”
      // 0=正常经营，1=非正常经营
      // 本月正常营业 && (下月非正常营业 || 下个月不存在的数据）
      // diffData(data, preAllData);

      for (Map<String, Object> cacheMap : data) {
        if (cacheMap.isEmpty()) {
          continue;
        }
        // 从第二个sheet开始
        // 新开门店定义：之前月份没有的门店 or 之前月份正常营业状态为0这个月变为1
        // 新关门店定义：正常经营状态从1变为 0
        final Object id = cacheMap.get("点评商户id");
        final Map<String, Object> preData = getPreData(preAllData, id);
        // 之前月份没有的门店
        if (preData.isEmpty()) {
          // 新开店
          cacheMap.put("是否新开", 1);
          cacheMap.put("本月存在且关店", 0);
          // 不是新关店
        } else {
          final Object o = preData.get("是否正常营业");
          final Object o1 = cacheMap.get("是否正常营业");

          // 之前月份正常营业状态为0这个月变为1
          if (o1 != null && o != null &&
              "1".equals(o1.toString()) && "0".equals(o.toString())) {
            // 新开店
            cacheMap.put("是否新开", 1);
          } else {
            // 不是新开
            cacheMap.put("是否新开", 0);
          }

          // 正常经营状态从1变为0
          if (o1 != null && o != null &&
              "0".equals(o1.toString()) && "1".equals(o.toString())) {
            // 新关店
            cacheMap.put("本月存在且关店", 1);
          } else {
            // 不是新关店
            cacheMap.put("本月存在且关店", 0);
          }
        }

        batch.add(cacheMap);
      }
      batch = distinct(batch);

      if (!batch.isEmpty()) {
        mongoPlusMapMapper.saveBatch("dp2", batch);
      }
      // 一个sheet处理完成
      preAllData = data;
    }
  }

  @Test
  void processDp2() {
    List<Map<String, Object>> data = getAllData("2024-11");
    final List<Map<String, Object>> dp2Data = distinct(data);
    mongoPlusMapMapper.saveBatch("dp2", dp2Data);
  }

  @Test
  void appendNext01() {
    String[] monthArrTemp = new String[]{
//        "2022-09",
//        "2022-10",
//        "2022-11",
//        "2022-12",
//        "2023-01",
//        "2023-02",
//        "2023-03",
//        "2023-04",
        "2024-06",
        "2024-07",
        "2024-08"
    };
    for (int i = 0; i < monthArrTemp.length - 1; i++) {
      log.info("m1 {} m2 {}", monthArrTemp[i], monthArrTemp[i + 1]);
      final List<Map<String, Object>> pre = getAllData2(monthArrTemp[i]);
      final List<Map<String, Object>> data = getAllData2(monthArrTemp[i + 1]);
      diffData(data, pre);
    }
  }

  @Test
  void appendNext02() {
    String[] monthArrTemp = new String[]{
//        "2023-04",
//        "2023-05",
//        "2023-06",
//        "2023-07",
//        "2023-08",
//        "2023-09",
        "2023-10",
        "2023-11",
        "2023-12"
    };
    for (int i = 0; i < monthArrTemp.length - 1; i++) {
      log.info("m1 {} m2 {}", monthArrTemp[i], monthArrTemp[i + 1]);
      final List<Map<String, Object>> pre = getAllData2(monthArrTemp[i]);
      final List<Map<String, Object>> data = getAllData2(monthArrTemp[i + 1]);
      diffData(data, pre);
    }
  }

  @Test
  void appendNext03() {
    String[] monthArrTemp = new String[]{
//        "2023-12",
//        "2024-01",
//        "2024-02",
//        "2024-03",
//        "2024-04",
//        "2024-05",
//        "2024-06",
//        "2024-07",
        "2024-10",
        "2024-11"
    };
    for (int i = 0; i < monthArrTemp.length - 1; i++) {
      log.info("m1 {} m2 {}", monthArrTemp[i], monthArrTemp[i + 1]);
      final List<Map<String, Object>> pre = getAllData2(monthArrTemp[i]);
      final List<Map<String, Object>> data = getAllData2(monthArrTemp[i + 1]);
      diffData(data, pre);
    }
  }


  private void diffData(List<Map<String, Object>> data, List<Map<String, Object>> preAllData) {
    final Iterator<Map<String, Object>> iterator = preAllData.parallelStream().iterator();
    while (iterator.hasNext()) {
      final Map<String, Object> preData = iterator.next();
      final Object id = preData.get("点评商户id");
      final Optional<Map<String, Object>> first = data.parallelStream()
          .filter(item -> item.get("点评商户id").equals(id))
          .findFirst();

      // 上个月有的数据，这个月没有，那么不补充到本月来，仅在上个月的历史数据中增加一个字段：“下月非正常经营”
      // 0=正常经营，1=非正常经营
      // 本月正常营业 && (下月非正常营业 || 下个月不存在的数据）
      final Object o1 = preData.get("是否正常营业");
      if (o1 != null && "1".equals(o1.toString()) && (first.isEmpty()
          || first.get().get("是否正常营业") == null
          || !"1".equals(first.get().get("是否正常营业").toString()))) {
        preData.put("下月非正常经营", 1);
      } else if (o1 == null || !"1".equals(o1.toString())) {
        preData.put("下月非正常经营", 1);
      } else {
        preData.put("下月非正常经营", 0);
      }

      mongoPlusMapMapper.updateById("dp2", preData);

      iterator.remove();
    }

    final Iterator<Map<String, Object>> dataIterator = data.parallelStream().iterator();
    while (dataIterator.hasNext()) {
      final Map<String, Object> cacheMap = dataIterator.next();
      final Map<String, Object> preData = getPreData(preAllData, cacheMap.get("点评商户id"));
      // 新开店定义 = 上月不存在 && 本月正常经营1 + 上月存在正常经营 0 -> 1
      if (preData.isEmpty()) {
        // 新开店
        if ("1".equals(cacheMap.get("是否正常营业"))) {
          cacheMap.put("是否新开", 1);
        } else {
          cacheMap.put("是否新开", 0);
        }
        cacheMap.put("本月存在且关店", 0);
        // 不是新关店
      } else {
        final Object o = preData.get("是否正常营业");
        final Object o1 = cacheMap.get("是否正常营业");

        // 之前月份正常营业状态为0这个月变为1
        if (o1 != null && o != null &&
            "1".equals(o1.toString()) && "0".equals(o.toString())) {
          // 新开店
          cacheMap.put("是否新开", 1);
        } else {
          // 不是新开
          cacheMap.put("是否新开", 0);
        }

        // 正常经营状态从1变为0
        if (o1 != null && o != null &&
            "0".equals(o1.toString()) && "1".equals(o.toString())) {
          // 新关店
          cacheMap.put("本月存在且关店", 1);
        } else {
          // 不是新关店
          cacheMap.put("本月存在且关店", 0);
        }
      }
      mongoPlusMapMapper.updateById("dp2", cacheMap);
      dataIterator.remove();
    }
  }


  @Test
  void diffHead() {
    final List<String> head10 = RedisUtils.getCacheList("dp:202410:head");
    final List<String> head11 = RedisUtils.getCacheList("dp:202411:head");
    final Collection<String> subtract = CollUtil.subtract(head10, head11);
    log.info("10 有 11 没有的部分");
    subtract.forEach(System.out::println);
    log.info("11 有 10 没有的部分");
    CollUtil.subtract(head11, head10).forEach(System.out::println);
  }

  /**
   * 多个sheet一起读取.
   */
  @Test
  void noModelReadInit() {
    final InputStream resourceAsStream = this.getClass().getClassLoader()
        .getResourceAsStream("基础数据.xlsx");
    init(resourceAsStream);

    assertThat(resourceAsStream).isNotNull();
  }

  // 多个sheet
  void init(InputStream resourceAsStream) {
    try (ExcelReader excelReader = EasyExcel.read(resourceAsStream).build()) {
      List<ReadSheet> readSheetList = new ArrayList<>();
      for (String s : sheetArr) {
        readSheetList.add(
            EasyExcel.readSheet(s)
                .registerReadListener(new NoModelDataListenerV2(s, mongoPlusMapMapper)).build());
      }
      // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
      excelReader.read(readSheetList);
    }

    assertThat(resourceAsStream).isNotNull();
  }

  // 读取商户名称
  @Test
  void noModelRead2() {
    final InputStream resourceAsStream = this.getClass().getClassLoader()
        .getResourceAsStream("处理后数据202410.xlsx");
    try (ExcelReader excelReader = EasyExcel.read(resourceAsStream).build()) {
      List<ReadSheet> readSheetList = new ArrayList<>();
      for (String s : sheetArr) {
        readSheetList.add(
            EasyExcel.readSheet(s)
                .registerReadListener(new NoModelDataListenerV3(s)).build());
      }
      excelReader.read(readSheetList);
    }

    assertThat(resourceAsStream).isNotNull();
  }

  @Test
  void noModelRead3() {
    final InputStream resourceAsStream = this.getClass().getClassLoader()
        .getResourceAsStream("处理后数据202411.xlsx");

    EasyExcel.read(resourceAsStream, new NoModelDataListenerV3("202411"))
        .sheet().doRead();

    assertThat(resourceAsStream).isNotNull();
  }

  @Test
  void mongo() {
    final List<Map<String, Object>> cachedDataList = new ArrayList<>();
    cachedDataList.add(Map.of("_id", "1001", "a", "1", "b", "2", "c", "3"));
    cachedDataList.add(Map.of("_id", "1002", "a", "1", "b", "2", "c", "3"));
    boolean saved = mongoPlusMapMapper.saveOrUpdateBatch("test", cachedDataList);
    assertThat(saved).isFalse();
  }

  @Test
  void dynamicHeadWrite() {
    // 方法1: 如果写到同一个sheet
    String fileName = "d://基础数据(处理).xlsx";
    // 这里 需要指定写用哪个class去写
    try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
      for (int i = 0; i < sheetArr.length; i++) {
        final String sheetName = sheetArr[i];
        WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).head(head(sheetName)).build();
        String keyPrefix = "DP:DATA:" + sheetName + ":";
        String headKey = "dp:" + sheetName + ":head";

        // 所有数据的key
        final Collection<String> keys = RedisUtils.keys(keyPrefix + "*");

        int BATCH_COUNT = 5000;
        List<Collection<Object>> batch = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

        // 表头
        final List<String> headCols = RedisUtils.getCacheList(headKey);
        // 补充是否新开、本月存在且关店
        appendHead(headCols);

        for (String key : keys) {
          // 每一行数据
          final Map<String, Object> cacheMap = RedisUtils.getCacheMap(key);
          Collection<Object> values = new ArrayList<>();

          headCols.stream().filter(
              col -> !"是否新开".equals(col) && !"本月存在且关店".equals(col)
          ).forEach(col -> values.add(format(cacheMap.getOrDefault(col, ""))));
          // 从第二个sheet开始
          // 如果数据在之前已经存在，判断本次本月存在且关店
          // 如果数据是不存在, 标记新开
          if (i > 0) {
            final Object id = cacheMap.get("点评商户id");
            final Map<String, Object> preData = RedisUtils.getCacheMap(
                "DP:DATA:" + sheetArr[i - 1] + ":" + id);
            if (preData.isEmpty()) {
              // 新开店
              values.add(1);
              // 不是新关店
              values.add(0);
            } else {
              // 不是新开
              values.add(0);
              final Object o = preData.get("是否正常营业");
              final Object o1 = cacheMap.get("是否正常营业");
              if ("0".equals(o1.toString()) && "1".equals(o.toString())) {
                // 新关店
                values.add(1);
              } else {
                // 不是新关店
                values.add(0);
              }
            }
          }

          if (batch.size() >= BATCH_COUNT) {
            excelWriter.write(batch, writeSheet);
            batch.clear();
          } else {
            batch.add(values);
          }
        }
        if (!batch.isEmpty()) {
          excelWriter.write(batch, writeSheet);
        }
      }
    }
    assertThat(fileName).isNotNull();
  }

  @Test
  void dynamicHeadWriteForMongo() {
    String fileName = "d://基础数据(处理)5.xlsx";

    try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
      List<Map<String, Object>> preAllData = null;

      for (int i = 0; i < sheetArr.length; i++) {
        final String sheetName = sheetArr[i];
        log.info("正在处理{}", sheetName);
        WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).head(head(sheetName)).build();
        String headKey = "dp:" + sheetName + ":head";

        int BATCH_COUNT = 10000;
        List<Collection<Object>> batch = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

        // 表头
        final List<String> headCols = RedisUtils.getCacheList(headKey);
        // 补充是否新开、本月存在且关店
        appendHead(headCols);

        // 这个sheet页的全部数据
        List<Map<String, Object>> data = getAllData(monthArr[i]);
        // 补充上个月有的,这个月没有的数据
        data = mergeData(data, preAllData);

        for (Map<String, Object> cacheMap : data) {
          // 每一行数据
          Collection<Object> values = new ArrayList<>();

          headCols.stream().filter(
              col -> !"是否新开".equals(col) && !"本月存在且关店".equals(col)
          ).forEach(col -> values.add(format(cacheMap.getOrDefault(col, ""))));
          // 从第二个sheet开始
          // 新开门店定义：之前月份没有的门店 or 之前月份正常营业状态为0这个月变为1
          // 新关门店定义：正常经营状态从1变为0 or 上个月有这家门店这个月没有了，这个数据要补充回本月的原数据中
          if (preAllData != null) {
            final Object id = cacheMap.get("点评商户id");
            final Map<String, Object> preData = getPreData(preAllData, id);
            // 之前月份没有的门店
            if (preData.isEmpty()) {
              // 新开店
              values.add(1);
              // 不是新关店
              values.add(0);
            } else {
              final Object o = preData.get("是否正常营业");
              final Object o1 = cacheMap.get("是否正常营业");

              // 之前月份正常营业状态为0这个月变为1
              if ("1".equals(o1.toString()) && "0".equals(o.toString())) {
                // 新开店
                values.add(1);
              } else {
                // 不是新开
                values.add(0);
              }

              // 正常经营状态从1变为0
              if ("0".equals(o1.toString()) && "1".equals(o.toString())) {
                // 新关店
                values.add(1);
              } else {
                // 不是新关店
                values.add(0);
              }
            }
          }

          if (batch.size() >= BATCH_COUNT) {
            excelWriter.write(batch, writeSheet);
            batch.clear();
          } else {
            batch.add(values);
          }
        }

        if (!batch.isEmpty()) {
          excelWriter.write(batch, writeSheet);
        }
        // 一个sheet处理完成
        preAllData = data;
      }
    }

    assertThat(fileName).isNotEmpty();
  }

  /**
   * 从数据库读取后直接写入 20240727.
   */
  @Test
  void dynamicHeadWriteForMongo20240627() {
    String fileName = "d://基础数据(处理)9.xlsx";

    try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
      for (int i = 0; i < sheetArr.length; i++) {

        final String sheetName = sheetArr[i];
        log.info("正在处理{}", sheetName);

        WriteSheet writeSheet = EasyExcel.writerSheet(i, sheetName).head(head(sheetName)).build();
        String headKey = "dp:" + sheetName + ":head";

        int batchCount = 10000;
        List<Collection<Object>> batch = ListUtils.newArrayListWithExpectedSize(batchCount);

        // 表头
        final List<String> headCols = RedisUtils.getCacheList(headKey);
        // 补充是否新开、本月存在且关店
        appendHead(headCols);

        // 这个sheet页的全部数据
        List<Map<String, Object>> data = getAllData2(monthArr[i]);

        for (Map<String, Object> cacheMap : data) {
          // 每一行数据
          Collection<Object> values = new ArrayList<>();

          headCols.forEach(col -> values.add(format(cacheMap.getOrDefault(col, ""))));

          if (batch.size() >= batchCount) {
            excelWriter.write(batch, writeSheet);

            batch.clear();
          } else {
            batch.add(values);
          }
        }

        if (!batch.isEmpty()) {
          excelWriter.write(batch, writeSheet);
        }
      }
    }

    assertThat(fileName).isNotEmpty();
  }

  @Test
  void dynamicHeadWriteForMongo20240822() {
    for (int i = 0; i < sheetArr.length; i++) {
      final String sheetName = sheetArr[i];
      log.info("正在处理{}", sheetName);
      try (ExcelWriter excelWriter = EasyExcel
          .write("d://处理后数据" + sheetName + ".xlsx").build()) {

        WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head(sheetName))
            .build();

        String headKey = "dp:" + sheetName + ":head";

        int batchCount = 10000;
        List<Collection<Object>> batch = ListUtils.newArrayListWithExpectedSize(batchCount);

        // 表头
        final List<String> headCols = RedisUtils.getCacheList(headKey);
        // 补充是否新开、本月存在且关店
        appendHead(headCols);

        // 这个sheet页的全部数据
        List<Map<String, Object>> data = getAllData2(monthArr[i]);

        for (Map<String, Object> cacheMap : data) {
          // 每一行数据
          Collection<Object> values = new ArrayList<>();

          headCols.forEach(col -> values.add(format(cacheMap.getOrDefault(col, ""))));

          if (batch.size() >= batchCount) {
            excelWriter.write(batch, writeSheet);

            batch.clear();
          } else {
            batch.add(values);
          }
        }

        if (!batch.isEmpty()) {
          excelWriter.write(batch, writeSheet);
        }
      }
    }
  }

  @Test
  void x20230506() {
    final String sheetName = "202411";
    final String sheetName2 = "2024-11";
    log.info("正在处理{}", sheetName);
    try (ExcelWriter excelWriter = EasyExcel
        .write("d://处理后数据" + sheetName + ".xlsx").build()) {

      WriteSheet writeSheet = EasyExcel.writerSheet(sheetName).head(head(sheetName))
          .build();

      String headKey = "dp:" + sheetName + ":head";

      int batchCount = 10000;
      List<Collection<Object>> batch = ListUtils.newArrayListWithExpectedSize(batchCount);

      // 表头
      final List<String> headCols = RedisUtils.getCacheList(headKey);
      // 补充是否新开、本月存在且关店
      appendHead(headCols);

      // 这个sheet页的全部数据
      List<Map<String, Object>> data = getAllData2(sheetName2);

      for (Map<String, Object> cacheMap : data) {
        // 每一行数据
        Collection<Object> values = new ArrayList<>();

        headCols.forEach(col -> values.add(format(cacheMap.getOrDefault(col, ""))));

        if (batch.size() >= batchCount) {
          excelWriter.write(batch, writeSheet);

          batch.clear();
        } else {
          batch.add(values);
        }
      }

      if (!batch.isEmpty()) {
        excelWriter.write(batch, writeSheet);
      }
    }
  }


  private List<Map<String, Object>> getAllData2(String sheetName) {
    final QueryWrapper<Map<String, Object>> wrapper = new QueryWrapper<>();
    wrapper.eq("月份", sheetName);

    final List<Map<String, Object>> dp202209 = mongoPlusMapMapper.list("dp2", wrapper);
    return dp202209.parallelStream()
        .collect(Collectors.collectingAndThen(
            Collectors.toCollection(
                () -> new TreeSet<>(Comparator.comparing(o -> o.get("点评商户id").toString()))
            ),
            ArrayList::new
        ));
  }


  /**
   * 20240627 写入比较的后的数据到数据库.
   */
  @Test
  void tempSave() {
    String[] sheetArrTemp = new String[]{
        "202407"
    };

    String[] monthArrTemp = new String[]{
        "2024-07"
    };

    List<Map<String, Object>> preAllData = getAllData2("2024-06");

    for (int i = 0; i < sheetArrTemp.length; i++) {
      final String sheetName = sheetArrTemp[i];
      log.info("正在处理{}", sheetName);
      String headKey = "dp:" + sheetName + ":head";

      List<Map<String, Object>> batch = new ArrayList<>();

      // 表头
      final List<String> headCols = RedisUtils.getCacheList(headKey);
      // 补充是否新开、本月存在且关店
      appendHead(headCols);

      // 这个sheet页的全部数据
      List<Map<String, Object>> data = getAllData(monthArrTemp[i]);
      // 补充上个月有的,这个月没有的数据
      data = mergeData(data, preAllData);
      log.info("合并后数据数量 -> {}", data.size());

      for (Map<String, Object> cacheMap : data) {
        if (cacheMap.isEmpty()) {
          continue;
        }
        // 从第二个sheet开始
        // 新开门店定义：之前月份没有的门店 or 之前月份正常营业状态为0这个月变为1
        // 新关门店定义：正常经营状态从1变为0 or 上个月有这家门店这个月没有了，这个数据要补充回本月的原数据中
        if (preAllData != null) {
          final Object id = cacheMap.get("点评商户id");
          final Map<String, Object> preData = getPreData(preAllData, id);
          // 之前月份没有的门店
          if (preData.isEmpty()) {
            // 新开店
            cacheMap.put("是否新开", 1);
            cacheMap.put("本月存在且关店", 0);
            // 不是新关店
          } else {
            final Object o = preData.get("是否正常营业");
            final Object o1 = cacheMap.get("是否正常营业");

            // 之前月份正常营业状态为0这个月变为1
            if ("1".equals(o1.toString()) && "0".equals(o.toString())) {
              // 新开店
              cacheMap.put("是否新开", 1);
            } else {
              // 不是新开
              cacheMap.put("是否新开", 0);
            }

            // 正常经营状态从1变为0
            if ("0".equals(o1.toString()) && "1".equals(o.toString())) {
              // 新关店
              cacheMap.put("本月存在且关店", 1);
            } else {
              // 不是新关店
              cacheMap.put("本月存在且关店", 0);
            }
          }
        } else {
          cacheMap.put("是否新开", 1);
          cacheMap.put("本月存在且关店", 0);
        }

        batch.add(cacheMap);
      }
      batch = distinct(batch);

      if (!batch.isEmpty()) {
        // mongoPlusMapMapper.saveBatch("dp2", batch);
      }
      // 一个sheet处理完成
      preAllData = data;
    }
  }


  private List<Map<String, Object>> mergeData(List<Map<String, Object>> data,
      List<Map<String, Object>> preAllData) {
    List<Map<String, Object>> mergeData = new ArrayList<>(data);
    if (preAllData != null) {
      for (Map<String, Object> preData : preAllData) {
        final Object id = preData.get("点评商户id");
        final boolean noneMatch = data.parallelStream()
            .noneMatch(item -> item.get("点评商户id").equals(id));
        if (noneMatch) {
          mergeData.add(preData);
        }
      }
    }
    return mergeData;
  }

  /**
   * 品牌数据.
   */
  @Test
  void dynamicWrite() {
    // 方法1: 如果写到同一个sheet
    String fileName = "d://品牌数据.xlsx";
    try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
      List<Map<String, Object>> brandAllData = getBrandData();
      final String sheetName = "品牌名";
      log.info("正在处理{}", sheetName);
      WriteSheet writeSheet = EasyExcel.writerSheet(0, sheetName).head(brandHead()).build();

      int batchCount = 5000;
      List<Collection<Object>> batch = ListUtils.newArrayListWithExpectedSize(batchCount);

      // 表头
      final List<String> headCols = new ArrayList<>();
      headCols.add("月份");
      headCols.add("品牌名");
      headCols.add("品牌标签");

      for (Map<String, Object> cacheMap : brandAllData) {
        // 每一行数据
        Collection<Object> values = new ArrayList<>();
        headCols
            .forEach(col -> values.add(format(cacheMap.getOrDefault(col, ""))));

        if (batch.size() >= batchCount) {
          excelWriter.write(batch, writeSheet);
          batch.clear();
        } else {
          batch.add(values);
        }
      }
      if (!batch.isEmpty()) {
        excelWriter.write(batch, writeSheet);
      }
    }
  }

  private List<List<String>> brandHead() {
    return List.of(
        List.of("月份"),
        List.of("品牌名"),
        List.of("品牌标签")
    );

  }

  private List<Map<String, Object>> getBrandData() {
    final Collection<String> keys = RedisUtils.keys("DP:BRAND:*");
    return keys
        .parallelStream()
        .map(RedisUtils::getCacheMap)
        .sorted(Comparator.comparing(o -> o.keySet().iterator().next()))
        .toList();
  }

  private Map<String, Object> getPreData(List<Map<String, Object>> preAllData, Object id) {
    return preAllData.parallelStream().filter(map -> ObjUtil.equals(map.get("点评商户id"), id))
        .findFirst()
        .orElse(Collections.emptyMap());
  }

  private List<Map<String, Object>> distinct(List<Map<String, Object>> data) {
    return data.parallelStream()
        .peek(m -> m.remove("_id"))
        .collect(Collectors.collectingAndThen(
            Collectors.toCollection(
                () -> new TreeSet<>(Comparator.comparing(o -> o.get("点评商户id").toString()))
            ),
            ArrayList::new
        ));
  }

  private List<Map<String, Object>> getAllData(String sheetName) {
    final QueryWrapper<Map<String, Object>> wrapper = new QueryWrapper<>();
    wrapper.eq("月份", sheetName);

    final List<Map<String, Object>> dp202209 = mongoPlusMapMapper.list("dp", wrapper);
    return distinct(dp202209);
  }

  @Test
  void persist2mongo() {
    int BATCH_COUNT = 10000;
    List<Map<String, Object>> cachedDataList = ListUtils.newArrayListWithExpectedSize(
        BATCH_COUNT);
    for (final String sheetName : sheetArr) {
      String keyPrefix = "DP:DATA:" + sheetName + ":";

      // 所有数据的key
      final Collection<String> keys = RedisUtils.keys(keyPrefix + "*");
      for (String key : keys) {
        final Map<String, Object> cacheMap = RedisUtils.getCacheMap(key);
        cachedDataList.add(cacheMap);
        if (cachedDataList.size() >= BATCH_COUNT) {
          saveData(cachedDataList);
          cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
      }
      if (!cachedDataList.isEmpty()) {
        saveData(cachedDataList);
      }
    }
  }

  @Test
  void diffBrand() {
    final List<Map<String, Object>> allData2 = getAllData2("2024-06");
    final List<String> brand01 = allData2.parallelStream().map(m -> m.get("品牌名").toString())
        .distinct()
        .toList();
    final List<String> brand02 = getBrandData().parallelStream()
        .map(m -> m.get("品牌名").toString()).toList();
    log.info("size ->{}, {}", brand01.size(), brand02.size());

//    brand01.parallelStream().filter(b -> !brand02.contains(b))
//        .forEach(b -> log.info("品牌名: {}", b));

    Set<String> brandName = new HashSet<>();
    allData2.parallelStream().forEach(m -> {
      if (!brandName.add(m.get("品牌名").toString())) {
        log.info("品牌名: {}", m.get("品牌名").toString());
      }
    });

  }

  private void saveData(List<Map<String, Object>> cachedDataList) {
    mongoPlusMapMapper.saveBatch("dp", cachedDataList);
    log.info("批量保存数据到mongo db: {}", cachedDataList.size());
  }

  private Object format(Object v) {
    if (NumberUtil.isInteger(v.toString())) {
      return NumberUtil.parseInt(v.toString());
    }
    if (NumberUtil.isDouble(v.toString())) {
      return NumberUtil.parseDouble(v.toString());
    }
    return v;
  }

  private List<List<String>> head(String sheetName) {
    List<List<String>> list = new ArrayList<>();
    String headKey = "dp:" + sheetName + ":head";
    final List<String> strings = RedisUtils.getCacheList(headKey);
    appendHead(strings);
    for (String string : strings) {
      list.add(List.of(string));
    }
    return list;
  }

  private void appendHead(List<String> head) {
    if (!head.contains("是否新开")) {
      head.add("是否新开");
    }
    if (!head.contains("本月存在且关店")) {
      head.add("本月存在且关店");
    }
    if (!head.contains("下月非正常经营")) {
      head.add("下月非正常经营");
    }
    head.remove("是否关店");
    head.remove("下月是否存在");
  }

  @Test
  void dp202404() {
    // 读取数据库数据
    final List<Map<String, Object>> allDataDp = getAllData("2024-04");
    final List<Map<String, Object>> allDataDp2 = getAllData2("2024-04");
    // 补充字段属性
    for (Map<String, Object> stringObjectMap : allDataDp2) {
      allDataDp.parallelStream()
          .filter(o -> o.get("点评商户id").equals(stringObjectMap.get("点评商户id")))
          .forEach(o ->
              {
                log.info("更新前 ->{}", stringObjectMap);
                stringObjectMap.putAll(o);
                log.info("更新后 ->{}", stringObjectMap);
                mongoPlusMapMapper.updateById("dp2", stringObjectMap);
              }
          );
    }

    // 更新数据到数据库

  }
}
